﻿create PROCEDURE Sync.SP_CommitteeBankAccountMainBranchDeposites_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteeBankAccountMainBranchDeposites_Temp TABLE 
		(
			CommitteeBankAccountMainBranchDepositID UniqueIdentifier,
			[CommitteeBankAccountID] uniqueidentifier,
				[DepositDate] datetime,
				[DepositAmount] float,
				[OperationNumber] nvarchar(50),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteeBankAccountMainBranchDeposites_Temp 
	   SELECT	Tbl.Col.value('CommitteeBankAccountMainBranchDepositID[1]','uniqueidentifier') CommitteeBankAccountMainBranchDepositID,
                Tbl.Col.value('CommitteeBankAccountID[1]', 'uniqueidentifier') [CommitteeBankAccountID],
Tbl.Col.value('DepositDate[1]', 'datetime') [DepositDate],
Tbl.Col.value('DepositAmount[1]', 'float') [DepositAmount],
Tbl.Col.value('OperationNumber[1]', 'nvarchar(50)') [OperationNumber],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteeBankAccountMainBranchDeposites') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteeBankAccountMainBranchDeposites] ([CommitteeBankAccountMainBranchDepositID],[CommitteeBankAccountID],[DepositDate],[DepositAmount],[OperationNumber])
	SELECT t.[CommitteeBankAccountMainBranchDepositID],t.[CommitteeBankAccountID],t.[DepositDate],t.[DepositAmount],t.[OperationNumber]
	FROM  @CommitteeBankAccountMainBranchDeposites_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteeBankAccountMainBranchDeposites_Tracking s
        WHERE t.CommitteeBankAccountMainBranchDepositID = s.CommitteeBankAccountMainBranchDepositID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteeBankAccountMainBranchDeposites]
	SET [CommitteeBankAccountMainBranchDeposites].[CommitteeBankAccountID] = t.[CommitteeBankAccountID],[CommitteeBankAccountMainBranchDeposites].[DepositDate] = t.[DepositDate],[CommitteeBankAccountMainBranchDeposites].[DepositAmount] = t.[DepositAmount],[CommitteeBankAccountMainBranchDeposites].[OperationNumber] = t.[OperationNumber] 
	FROM [dbo].[CommitteeBankAccountMainBranchDeposites] s JOIN @CommitteeBankAccountMainBranchDeposites_Temp t 
    ON t.CommitteeBankAccountMainBranchDepositID = s.CommitteeBankAccountMainBranchDepositID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteeBankAccountMainBranchDeposites_Tracking r
				  WHERE t.CommitteeBankAccountMainBranchDepositID = r.CommitteeBankAccountMainBranchDepositID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteeBankAccountMainBranchDeposites]
    FROM @CommitteeBankAccountMainBranchDeposites_Temp t JOIN [dbo].[CommitteeBankAccountMainBranchDeposites] s
    ON t.CommitteeBankAccountMainBranchDepositID = s.CommitteeBankAccountMainBranchDepositID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END











